package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.Project;
import com.zzmcc.jms.model.ProjectSubmit;
import util.ChristStringUtil;
import util.ModelUtils;

import java.util.*;

public class ProjectSubmitController extends BaseController {
    private static final Logger log =  Logger.getLogger(ProjectSubmitController.class);

	public void index() {
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String name = getPara("project.name");
		String code = getPara("project.code");
		String owner = getPara("project.owner");
		String homeCounty = getPara("project.homeCounty");
		String share = getPara("project.share");

		String isCm = getPara("project.isCm");
		String isCu = getPara("project.isCu");
		String isCt = getPara("project.isCt");

		String isPass = getPara("project.isPass");
		String isSubmit = getPara("project.isSubmit");

		String isCmSubmit = getPara("project.isCmSubmit");
		String isCuSubmit = getPara("project.isCuSubmit");
		String isCtSubmit = getPara("project.isCtSubmit");

		String isCmRent = getPara("project.isCmRent");
		String isCuRent = getPara("project.isCuRent");
		String isCtRent = getPara("project.isCtRent");

		String modifyDate = getPara("project.modifyDate");
		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(owner)){
			whee.append(" and owner like ?");
			param.add("%" + owner + "%");
		}
		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and a.code like ?");
			param.add("%" + code + "%");
		}
		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}
		if(ChristStringUtil.isNotEmpty(share)){
			whee.append(" and share = ?");
			param.add(share);
		}
		if(ChristStringUtil.isNotEmpty(isCm)){
			whee.append(" and COALESCE(isCm,'否') = ?");
			param.add(isCm);
		}
		if(ChristStringUtil.isNotEmpty(isCt)){
			whee.append(" and COALESCE(isCt,'否') = ?");
			param.add(isCt);
		}
		if(ChristStringUtil.isNotEmpty(isCu)){
			whee.append(" and COALESCE(isCu,'否') = ?");
			param.add(isCu);
		}

		if(ChristStringUtil.isNotEmpty(isPass)){
			whee.append(" and COALESCE(isPass,'否') = ?");
			param.add(isPass);
		}
		if(ChristStringUtil.isNotEmpty(isSubmit)){
			whee.append(" and COALESCE(isSubmit,'否') = ?");
			param.add(isSubmit);
		}

		if(ChristStringUtil.isNotEmpty(isCmSubmit)){
			whee.append(" and COALESCE(isCmSubmit,'否') = ?");
			param.add(isCmSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isCtSubmit)){
			whee.append(" and COALESCE(isCtSubmit,'否') = ?");
			param.add(isCtSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isCuSubmit)){
			whee.append(" and COALESCE(isCuSubmit,'否') = ?");
			param.add(isCuSubmit);
		}


		if(ChristStringUtil.isNotEmpty(isCmRent)){
			whee.append(" and COALESCE(isCmRent,'否') = ?");
			param.add(isCmRent);
		}
		if(ChristStringUtil.isNotEmpty(isCtRent)){
			whee.append(" and COALESCE(isCtRent,'否') = ?");
			param.add(isCtRent);
		}
		if(ChristStringUtil.isNotEmpty(isCuRent)){
			whee.append(" and COALESCE(isCuRent,'否') = ?");
			param.add(isCuRent);
		}

		if(ChristStringUtil.isNotEmpty(modifyDate)){
			whee.append(" and TO_DAYS(modifyDate) >= TO_DAYS(?)");
			param.add( modifyDate );
		}

		Page<ProjectSubmit> pager = ProjectSubmit.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
				"select a.id,homeCounty,name,a.code,longitude,latitude,addr,projectDate,a.towerType,a.share,isCm,isCt,isCu,c.isPass,c.isSubmit,a.memo," +
						"b.isCmSubmit,b.isCtSubmit,b.isCuSubmit,b.isCmRent,b.isCtRent,b.isCuRent,b.modifyDate,b.modifyMemo",
				" from jms_project a left join jms_project_submit b on b.code=a.code " +
						" left join jms_project_build c on c.pid=a.pid " +
						" where a.isProject='是' "+ whee.toString()+"order by b.modifyDate desc",
				param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("owner",owner);
		setAttr("code",code);
		setAttr("homeCounty",homeCounty);
		setAttr("share",share);

		setAttr("isCm",isCm);
		setAttr("isCu",isCu);
		setAttr("isCt",isCt);

		setAttr("isPass",isPass);
		setAttr("isSubmit",isSubmit);

		setAttr("isCmSubmit",isCmSubmit);
		setAttr("isCuSubmit",isCuSubmit);
		setAttr("isCtSubmit",isCtSubmit);

		setAttr("isCmRent",isCmRent);
		setAttr("isCuRent",isCuRent);
		setAttr("isCtRent",isCtRent);

		setAttr("modifyDate",modifyDate);
		render("../project_submit_list.html");
	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		String id = getPara("id");
		String sql="select a.name,a.code,a.isCm,a.isCt,a.isCu," +
				"b.id,b.isCmSubmit,b.isCtSubmit,b.isCuSubmit," +
				"b.isCmRent,b.isCtRent,b.isCuRent," +
				"b.cmSubmitDate,b.ctSubmitDate,b.cuSubmitDate," +
				"b.cmRentDate,b.ctRentDate,b.cuRentDate," +
				"b.modifyMemo,b.modifyDate"+
				" from jms_project a left join jms_project_submit b on b.code=a.code " +
				" where a.id=?";
		ProjectSubmit m= ProjectSubmit.dao.findFirst(sql,id);

		setAttr("projectSubmit",m);
		render("../project_submit_input.html");

	}

	public void update() {
		ProjectSubmit m = getModel(ProjectSubmit.class, "projectSubmit");
		m.update();
		renderDWZSuccessJson("修改成功！");
	}

	public void save() {
		try {
			ProjectSubmit m = getModel(ProjectSubmit.class, "projectSubmit");
			m.save();
			renderDWZSuccessJson("添加成功！");
		}catch (Exception e){
			renderDWZSuccessJson("重复提交，失败！");
		}
	}

	public void export(){
		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String isCmSubmit = getPara("project.isCmSubmit");
		String isCuSubmit = getPara("project.isCuSubmit");
		String isCtSubmit = getPara("project.isCtSubmit");

		if(ChristStringUtil.isNotEmpty(isCmSubmit)){
			whee.append(" and isCmSubmit = ?");
			param.add(isCmSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isCtSubmit)){
			whee.append(" and isCtSubmit = ?");
			param.add(isCtSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isCuSubmit)){
			whee.append(" and isCuSubmit = ?");
			param.add(isCuSubmit);
		}

		String sql = "select a.homeCounty,a.name,a.code,a.longitude,a.latitude,a.addr,a.towerType,a.share,a.projectDate,a.memo,b.modifyMemo,b.modifyDate," +
				"a.isCm,b.isCmSubmit,b.cmSubmitDate,isCmRent,cmRentDate,"+
				"a.isCt,b.isCtSubmit,b.ctSubmitDate,isCtRent,ctRentDate,"+
				"a.isCu,b.isCuSubmit,b.cuSubmitDate,isCuRent,cuRentDate "+
				" from jms_project a left join jms_project_submit b on b.code=a.code " +
				" where a.isProject='是' "+ whee.toString()+" order by b.modifyDate desc";
		List<Record> submitDetail = Db.find(sql, param.toArray());

		sql ="select date_format(now(),'%Y%m%d') sumDate, homeCounty,(cmCnt+ctCnt+cuCnt) cnt,submitCnt,rentCnt,\n" +
				"(cmSubmitCnt+ctSubmitCnt+cuSubmitCnt)/(cmCnt+ctCnt+cuCnt) submitRate,\n" +
				"(cmRentCnt+ctRentCnt+cuRentCnt)/(cmCnt+ctCnt+cuCnt) rentRate,\n" +
				"cmCnt,cmSubmitCnt,cmSubmitCnt/cmCnt cmSubmitRate,cmRentCnt,cmRentCnt/cmCnt cmRentRate,\n" +
				"ctCnt,ctSubmitCnt,ctSubmitCnt/ctCnt ctSubmitRate,ctRentCnt,ctRentCnt/cmCnt ctRentRate,\n" +
				"cuCnt,cuSubmitCnt,cuSubmitCnt/cuCnt cuSubmitRate,cuRentCnt,cuRentCnt/cmCnt cuRentRate\n" +
				"from (\n" +
				"select a.homeCounty,\n" +
				"sum(case when a.isCm='是' then 1 else 0 end) cmCnt,\n" +
				"sum(case when b.isCmSubmit='是' then 1 else 0 end) cmSubmitCnt,\n" +
				"sum(case when b.isCmRent='是' then 1 else 0 end) cmRentCnt,\n" +
				"sum(case when a.isCt='是' then 1 else 0 end) ctCnt,\n" +
				"sum(case when b.isCtSubmit='是' then 1 else 0 end) ctSubmitCnt,\n" +
				"sum(case when b.isCtRent='是' then 1 else 0 end) ctRentCnt,\n" +
				"sum(case when a.isCu='是' then 1 else 0 end) cuCnt,\n" +
				"sum(case when b.isCuSubmit='是' then 1 else 0 end) cuSubmitCnt,\n" +
				"sum(case when b.isCuRent='是' then 1 else 0 end) cuRentCnt,\n" +
				"sum(case when b.isCmSubmit='是' or b.isCtSubmit='是' or b.isCuSubmit='是'  then 1 else 0 end) submitCnt,\n" +
				"sum(case when b.isCmRent='是' or b.isCtRent='是' or b.isCuRent='是'  then 1 else 0 end) rentCnt\n" +
				"  from jms_project a left join jms_project_submit b\n" +
				"on a.code=b.code\n" +
				"where a.isProject='是'\n" +
				"group by a.homeCounty\n" +
				") t\n";
		List<Record> submitSum = Db.find(sql);


		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("submitDetail", submitDetail);
		beans.put("submitSum", submitSum);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_submit.xls";

		String filename = "project_submit.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}

	public void exportRent(){

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String homeCounty = getPara("project.homeCounty");

		String isCm = getPara("project.isCm");
		String isCu = getPara("project.isCu");
		String isCt = getPara("project.isCt");

		String isCmSubmit = getPara("project.isCmSubmit");
		String isCuSubmit = getPara("project.isCuSubmit");
		String isCtSubmit = getPara("project.isCtSubmit");

		String isCmRent = getPara("project.isCmRent");
		String isCuRent = getPara("project.isCuRent");
		String isCtRent = getPara("project.isCtRent");

		if(ChristStringUtil.isNotEmpty(isCm)){
			whee.append(" and COALESCE(isCm,'否') = ?");
			param.add(isCm);
		}
		if(ChristStringUtil.isNotEmpty(isCt)){
			whee.append(" and COALESCE(isCt,'否') = ?");
			param.add(isCt);
		}
		if(ChristStringUtil.isNotEmpty(isCu)){
			whee.append(" and COALESCE(isCu,'否') = ?");
			param.add(isCu);
		}

		if(ChristStringUtil.isNotEmpty(isCmSubmit)){
			whee.append(" and COALESCE(isCmSubmit,'否') = ?");
			param.add(isCmSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isCtSubmit)){
			whee.append(" and COALESCE(isCtSubmit,'否') = ?");
			param.add(isCtSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isCuSubmit)){
			whee.append(" and COALESCE(isCuSubmit,'否') = ?");
			param.add(isCuSubmit);
		}


		if(ChristStringUtil.isNotEmpty(isCmRent)){
			whee.append(" and COALESCE(isCmRent,'否') = ?");
			param.add(isCmRent);
		}
		if(ChristStringUtil.isNotEmpty(isCtRent)){
			whee.append(" and COALESCE(isCtRent,'否') = ?");
			param.add(isCtRent);
		}
		if(ChristStringUtil.isNotEmpty(isCuRent)){
			whee.append(" and COALESCE(isCuRent,'否') = ?");
			param.add(isCuRent);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}

		String sql="select a.homeCounty,a.name,a.cmName,a.ctName,a.cuName,a.code,a.longitude,a.latitude,a.addr,a.memo,\n" +
				"b.isCmSubmit,b.cmSubmitDate,b.isCmRent,b.cmRentDate, \n" +
				"b.isCtSubmit,b.ctSubmitDate,b.isCtRent,b.ctRentDate, \n" +
				"b.isCuSubmit,b.cuSubmitDate,b.isCuRent,b.cuRentDate, \n" +
				"b.modifyDate,b.modifyMemo\n" +
				" from jms_project a\n" +
				"left join\n" +
				"jms_project_submit b\n" +
				"on a.code=b.code\n" +
				"where a.isProject='是' "+ whee.toString()+" order by b.modifyDate desc";
		List<Record> rentDetail = Db.find(sql, param.toArray());

		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("rentDetail", rentDetail);
		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_rent.xls";
		String filename = "project_rent.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}

	@Before(AuthorizeInterceptor.class)
	public void transCmSubmit() {
		String[] ids = getParaValues("ids");
		for (String id : ids) {
			try
			{
				String sql = "select a.code,b.id,coalesce(b.isCmSubmit,'否') isCmSubmit, coalesce(c.submitDate,now()) cmSubmitDate\n " +
						" from jms_project a left join jms_project_submit b on b.code=a.code \n" +
						" left join jms_project_build c on c.pid=a.pid \n" +
						" where a.isCm='是' and c.isSubmit='是' and a.id=?";
				ProjectSubmit m = ProjectSubmit.dao.findFirst(sql,id);
				if (null == m.get("id"))
				{
					//没有交付信息
					m.set("isCmSubmit", "是");
					m.set("cmSubmitDate", m.get("cmSubmitDate"));
					m.set("modifyMemo","批量交付");
					ModelUtils.fillForUpdate(m);
					m.save();
				}
				else
				{
					String isCmSubmit = m.getStr("isCmSubmit");
					if (isCmSubmit.equals("否"))
					{
						m.set("isCmSubmit", "是");
						m.set("cmSubmitDate", m.get("cmSubmitDate"));
					}
					m.set("modifyMemo","批量交付");
					ModelUtils.fillForUpdate(m);
					m.update();
				}
			}
			catch (Exception e)
			{
				renderDWZSuccessJson("更新失败，请核实数据！");
				return;
			}
		}
		renderDWZSuccessJson("更新成功!");
	}

	@Before(AuthorizeInterceptor.class)
	public void transCtSubmit() {
		String[] ids = getParaValues("ids");
		for (String id : ids) {
			try
			{
				String sql = "select a.code,b.id,coalesce(b.isCtSubmit,'否') isCtSubmit, coalesce(c.submitDate,now()) ctSubmitDate\n " +
						" from jms_project a left join jms_project_submit b on b.code=a.code \n" +
						" left join jms_project_build c on c.pid=a.pid \n" +
						" where a.isCt='是' and c.isSubmit='是' and a.id=?";
				ProjectSubmit m = ProjectSubmit.dao.findFirst(sql,id);
				if (null == m.get("id"))
				{
					//没有交付信息
					m.set("isCtSubmit", "是");
					m.set("ctSubmitDate", m.get("ctSubmitDate"));
					m.set("modifyMemo","批量交付");
					ModelUtils.fillForUpdate(m);
					m.save();
				}
				else
				{
					String isCtSubmit = m.getStr("isCtSubmit");
					if (isCtSubmit.equals("否"))
					{
						m.set("isCtSubmit", "是");
						m.set("ctSubmitDate", m.get("ctSubmitDate"));
					}
					m.set("modifyMemo","批量交付");
					ModelUtils.fillForUpdate(m);
					m.update();
				}
			}
			catch (Exception e)
			{
				renderDWZSuccessJson("更新失败，请核实数据！");
				return;
			}
		}
		renderDWZSuccessJson("更新成功!");
	}

	@Before(AuthorizeInterceptor.class)
	public void transCuSubmit() {
		String[] ids = getParaValues("ids");
		for (String id : ids) {
			try
			{
				String sql = "select a.code,b.id,coalesce(b.isCuSubmit,'否') isCuSubmit, coalesce(c.submitDate,now()) cuSubmitDate\n " +
						" from jms_project a left join jms_project_submit b on b.code=a.code \n" +
						" left join jms_project_build c on c.pid=a.pid \n" +
						" where a.isCu='是' and c.isSubmit='是' and a.id=?";
				ProjectSubmit m = ProjectSubmit.dao.findFirst(sql,id);
				if (null == m.get("id"))
				{
					//没有交付信息
					m.set("isCuSubmit", "是");
					m.set("cuSubmitDate", m.get("cuSubmitDate"));
					m.set("modifyMemo","批量交付");
					ModelUtils.fillForUpdate(m);
					m.save();
				}
				else
				{
					String isCuSubmit = m.getStr("isCuSubmit");
					if (isCuSubmit.equals("否"))
					{
						m.set("isCuSubmit", "是");
						m.set("cuSubmitDate", m.get("cuSubmitDate"));
					}
					m.set("modifyMemo","批量交付");
					ModelUtils.fillForUpdate(m);
					m.update();
				}
			}
			catch (Exception e)
			{
				renderDWZSuccessJson("更新失败，请核实数据！");
				return;
			}
		}
		renderDWZSuccessJson("更新成功!");
	}

	@Before(AuthorizeInterceptor.class)
	public void delete() {

		String[] ids = getParaValues("ids");
		for(String id:ids){
			Project m =Project.dao.findById(id);
			Db.update("delete a from jms_project_submit a where code =?", m.get("code"));
		}
		renderDWZSuccessJson("删除成功!");

	}

}
